package dao.mysql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.CompensationLeaveDTO;
import model.LeaveDTO;
import dao.CompensationLeaveDAO;
import exception.DAOSystemException;

public class CompensationLeaveDAOImpl implements CompensationLeaveDAO {
	MySqlImpl mysqlImpl = new MySqlImpl();

	@Override
	public CompensationLeaveDTO find(String id) throws DAOSystemException {
		// TODO Auto-generated method stub

		String selectSql = "SELECT * FROM compensationleave WHERE id='"
				+ id.trim() + "';";
		CompensationLeaveDTO oneCompensationLeave = new CompensationLeaveDTO();
		try {
			ResultSet rs = mysqlImpl.get(selectSql);

			while (rs.next()) {
				oneCompensationLeave.setId(rs.getString("id"));
				oneCompensationLeave.setTotalOT(rs.getDouble("totalOT"));
				oneCompensationLeave.setConsumeOT(rs.getDouble("consumeOT"));
				oneCompensationLeave.setBalanceOT(rs.getDouble("balanceOT"));
			}

		} catch (Exception e) {
			String error = "Error selecting compensationleave. Nested Exception is: "
					+ e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

		return oneCompensationLeave;
	}

	@Override
	public ArrayList<CompensationLeaveDTO> findAllCompensationLeaves()
			throws DAOSystemException {
		// TODO Auto-generated method stub
		ArrayList<CompensationLeaveDTO> items = new ArrayList<CompensationLeaveDTO>();
		String selectSql = "SELECT * FROM compensationleave;";

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = mysqlImpl.get(selectSql);
			CompensationLeaveDTO oneCompensationLeave = null;
			String leaveTypeId = null;
			while (rs.next()) {
				oneCompensationLeave = new CompensationLeaveDTO();
				oneCompensationLeave.setId(rs.getString("id"));
				oneCompensationLeave.setTotalOT(rs.getDouble("totalOT"));
				oneCompensationLeave.setConsumeOT(rs.getDouble("consumeOT"));
				oneCompensationLeave.setBalanceOT(rs.getDouble("balanceOT"));
				items.add(oneCompensationLeave);
			}

		} catch (Exception e) {
			String error = "Error selecting compensationleave. Nested Exception is: "
					+ e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	@Override
	public void insertCompensationLeave(CompensationLeaveDTO compensationLeave)
			throws DAOSystemException {
		// TODO Auto-generated method stub
		String insertSql = "INSERT INTO compensationleave VALUES('"
				+ compensationLeave.getId() + "', '"
				+ compensationLeave.getTotalOT() + "', '"
				+ compensationLeave.getConsumeOT() + "', '"
				+ compensationLeave.getBalanceOT() + "');";
		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + insertSql);
			int res = mysqlImpl.execute(insertSql);

		} catch (Exception e) {
			String error = "Error selecting compensationleave. Nested Exception is: "
					+ e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}
	}

	@Override
	public void updateCompensationLeave(CompensationLeaveDTO compensationLeave)
			throws DAOSystemException {
		// TODO Auto-generated method stub
		String updateSql = "UPDATE compensationleave SET totalOT = '"
				+ compensationLeave.getTotalOT() + "', consumeOT = '"
				+ compensationLeave.getConsumeOT() + "', balanceOT = '"
				+ compensationLeave.getBalanceOT() + "' WHERE id='"
				+ compensationLeave.getId() + "';";

		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			int res = mysqlImpl.execute(updateSql);

		} catch (Exception e) {
			String error = "Error selecting compensationleave. Nested Exception is: "
					+ e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

	}

	@Override
	public void deleteCompensationLeave(CompensationLeaveDTO compensationLeave)
			throws DAOSystemException {
		// TODO Auto-generated method stub
		String deleteSql = "DELETE FROM compensationleave WHERE id='"
				+ compensationLeave.getId().trim() + "';";

		try {
			mysqlImpl.execute(deleteSql);
		} catch (SQLException e) {
			String error = "Error deleting compensationleave. Nested Exception is: "
					+ e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			try {
				mysqlImpl.rollBack();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

	}
	
	/* ********************************* */
	// Added by : Suganthi V Subramaniam
	/* ********************************* */
	@Override
	public void updateCompensationLeaveOT(CompensationLeaveDTO compensationLeave)
			throws DAOSystemException {
		// TODO Auto-generated method stub
		String updateSql = "UPDATE compensationleave SET balanceOT = "
				+ "balanceOT + " + "'" + compensationLeave.getBalanceOT() + "'"
				+ " WHERE id='" + compensationLeave.getId() + "';";

		// totalOT= consumeOT+balanceOT
		/*String updateBalSql = "UPDATE compensationleave SET totalOT = "
				+ "balanceOT + " + "consumeOT" + " WHERE id='"
				+ compensationLeave.getId() + "';";  */
		try {

			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			int res = mysqlImpl.execute(updateSql);
			//res = mysqlImpl.execute(updateBalSql);

		} catch (Exception e) {
			String error = "Error selecting compensationleave. Nested Exception is: "
					+ e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOSystemException(error);
		} finally {
			try {
				mysqlImpl.close();
			} catch (Exception e) {
			}
		}

	}

}
